USE [NANS21V]
GO

/****** Object:  StoredProcedure [dbo].[TeamMembers]    Script Date: 04/28/2014 10:10:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TeamMembers]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[TeamMembers]
GO

--トラックリザルト一覧のチーム表示に
CREATE PROCEDURE [dbo].[TeamMembers]
	@TAIKAINO int,
	@KYOGINO int, 
	@SUBNO int, 
	@ROUNDNO int, 
	@KUMINO int, 
	@LANE int 
AS
		SELECT KS.KYOGISYANO, 
		   O.SOUJUN, 
		   (convert(varchar, KS.NUMBER1) + convert(varchar, KS.NUMBER2)) AS NUMBER,
		   KS.KYOGISYAKANA as KYOGISYAKANA,
		   KS.KYOGISYAMEI AS KYOGISYAMEI, 
		   dbo.Seibetu(KS.SEIBETU) AS SEIBETU,
		   KS.GAKUNEN, 
		   KS.SEINEN, 
		   S.SYOZOKUMEI, 
		   KS.KOJINSYOZOKUTIMEI, 
		   ST.SYOZOKUTIMEI 
	  FROM TBL_ORDER AS O, 
	  	   TBL_KYOGISYA AS KS, 
	  	   TBL_SYOZOKU AS S, 
	  	   TBL_SYOZOKUTI AS ST 
	  WHERE O.TAIKAINO = KS.TAIKAINO 
		AND O.KYOGISYANO = KS.KYOGISYANO 
		AND KS.TAIKAINO = S.TAIKAINO 
		AND KS.SYOZOKUNO1 = S.SYOZOKUNO 
		AND S.SYOZOKUTINO = ST.SYOZOKUTINO 
		AND O.TAIKAINO = @TAIKAINO 
		AND O.KYOGINO = @KYOGINO 
		AND O.SUBNO = @SUBNO 
		AND O.ROUNDNO = @ROUNDNO 
		AND O.KUMINO = @KUMINO 
		AND O.LANE = @LANE 
	  ORDER BY O.LANE, 
	  		   O.SOUJUN